<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2016/6/23
 * Time: 11:14
 */
namespace Home\Model;
use Think\Model;
class StatisticModel extends Model
{
    protected $myModel;
    protected $stable_name = "lieying_install_statistic";
    protected $ctable_name = "lieying_install_channel";

    public function __construct()
    {
        $this -> myModel = new Model('','','DB_STATIC');
    }
    //用于每日统计执行脚本中创建统计表格
    public function createStatisticTable()
    {
        $table_name = "epduba_lieying_install_statistic";
        $sql = "CREATE TABLE IF NOT EXISTS %s (
                  `id` int(11) unsigned NOT NULL auto_increment,
                  `install` int(11) NOT NULL DEFAULT 0,
                  `uninstall` int(11) NOT NULL DEFAULT 0,
                  `active` int(11) NOT NULL DEFAULT 0,
                  `installun` int(11) NOT NULL DEFAULT 0,
                  `statistic_date` date NOT NULL,
                  PRIMARY KEY (`id`)
                 ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
        $sql = sprintf($sql, $table_name);
        try{
            $this -> myModel -> db -> execute($sql);
        }catch(\Think\Exception $e){
            \Think\Log::write('createTable:'.$table_name.' HAS EXISTS.','ERR');
        }
        return $table_name;
    }
    // 用于每日按渠道统计脚本
    public function createChannelTable(){
        $table_name = "epduba_lieying_install_channel";
        $sql = "CREATE TABLE IF NOT EXISTS %s (
                  `id` int(11) unsigned NOT NULL auto_increment,
                  `install` int(11) NOT NULL DEFAULT 0,
                  `uninstall` int(11) NOT NULL DEFAULT 0,
                  `active` int(11) NOT NULL DEFAULT 0,
                  `installun` int(11) NOT NULL DEFAULT 0,
                  `statistic_date` date NOT NULL,
                  `channel_id` char(10) NOT NULL,
                  PRIMARY KEY (`id`)
                 ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
        $sql = sprintf($sql, $table_name);
        try{
            $this -> myModel -> db -> execute($sql);
        }catch(\Think\Exception $e){
            \Think\Log::write('createTable:'.$table_name.' HAS EXISTS.','ERR');
        }
        return $table_name;
    }
//用于每日统计执行脚本中 信息统计
    public function dayDataStatistic($table_name)
    {
        $i_model = D('Install');
        $d_result =  $i_model -> getCountsByField($table_name, 'active');
        $i_data['statistic_date'] = date('Y-m-d',strtotime('-1 days'));
        if($d_result){
            foreach($d_result as $key => $val){
                switch($key){
                    case 1:
                        $i_data['install'] = $val;
                        break;
                    case 2:
                        $i_data['uninstall'] = $val;
                        break;
                    case 3:
                        $i_data['active'] = $val;
                        break;
                }
            }
        }
        $un_count = count($i_model ->getInstallUnCount($table_name));
        if($un_count > 0){
            $i_data['installun'] = $un_count;
        }
        $this -> myModel -> tableName = $this -> stable_name;
        try{
            $this -> myModel -> data($i_data) -> add();
        }catch(\Think\Exception $e){
            \Think\Log::write('dayDataStatistic:'.$e->getMessage(),'ERR');
        }
    }
    //按照channel 统计每日数据
    public function dayChannelDataStatic($table_name){
        $i_model = D('Install');
        $channel_array = array();
        // install uninstall active
        $data = $i_model -> getDayDataGroupChannel($table_name);
        foreach($data as $d){
            $channel_id = ($d['channel_id'])?:'0000';
            $channel_array[$channel_id]['statistic_date'] = date("Y-m-d",strtotime('-1 days'));
            switch($d['active']){
                case '1':
                    $channel_array[$channel_id]['install'] = $d['c_count'];
                    break;
                case '2':
                    $channel_array[$channel_id]['uninstall'] = $d['c_count'];
                    break;
                case '3':
                    $channel_array[$channel_id]['active'] = $d['c_count'];
                    break;
            }
        }
        // install&un
        $u_data = $i_model -> getInstallUnCountByChannel($table_name);
        foreach($u_data as $key => $cdata){
            $channel_array[$key]['statistic_date'] = date("Y-m-d",strtotime('-1 days'));
            $channel_array[$key]['installun'] = $cdata;
            $channel_array[$key]['install'] = ($channel_array[$key]['install'])?:0;
            $channel_array[$key]['uninstall'] = ($channel_array[$key]['uninstall'])?:0;
            $channel_array[$key]['active'] = ($channel_array[$key]['active'])?:0;
        }
        try{
            if(count($channel_array) > 0){
                $this -> myModel -> tableName = $this -> ctable_name;
                foreach($channel_array as $ckey => $carr){
                    $i_data = array();
                    $i_data['install'] = $carr['install'];
                    $i_data['active'] = $carr['active'];
                    $i_data['uninstall'] = $carr['uninstall'];
                    $i_data['installun'] = $carr['installun'];
                    $i_data['statistic_date'] = $carr['statistic_date'];
                    $i_data['channel_id'] = $ckey;
                    $this -> myModel -> data($i_data) -> add();
                }
            }
        }catch(\Think\Exception $e){
            \Think\Log::write('dayChannelDataStatic:'.$e->getMessage(),'ERR');
        }
    }
    // 获取时间段中的每天的统计数据
    public function getCountsDur($b_date,$e_date){
        $table_name = "epduba_lieying_install_statistic";
        $sql = "SELECT install,uninstall,active,installun,statistic_date FROM %s WHERE statistic_date >= '%s' AND statistic_date <= '%s'";
        $sql = sprintf($sql,$table_name,$b_date,$e_date);
        return $this -> myModel -> query($sql);
    }
    // 获取时间段中每天的固定channel 的统计数据
    public function getDayByChannel($channel_id,$b_date,$e_date){
        $table_name = "epduba_lieying_install_channel";
        $sql = "SELECT install,uninstall,active,installun,statistic_date FROM %s WHERE statistic_date >= '%s' AND statistic_date <= '%s' AND channel_id = '%s'";
        $sql = sprintf($sql,$table_name,$b_date,$e_date,$channel_id);
        return $this -> myModel -> query($sql);
    }
    //按时间段查询渠道的统计数据
    public function getChannelCountByDate($b_date, $e_date) {
        $table_name = "epduba_lieying_install_channel";
        $sql = "SELECT
            channel_id,
            sum(install) as install,
            sum(uninstall) as uninstall,
            sum(active) as active,
            sum(installun) as installun,
            statistic_date
        FROM %s
        WHERE statistic_date >= '%s' AND statistic_date <= '%s'
        GROUP BY channel_id
        ";
        $sql = sprintf($sql,$table_name,$b_date,$e_date);
//        echo $sql;
        return $this -> myModel -> query($sql);

    }
}